package com.zcyr.core.impl;

import com.zcyr.core.AbstractDatabaseEnvironment;
import com.zcyr.pojo.SQLResult;
import com.zcyr.util.SQLTrim;
import com.zcyr.util.StatementTool;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

@Component
class SQLCommandMethods {

    @Autowired
    DataSource dataSource;

    /**
     * 查询information_schema.schemata表出属于该环境的所有schema信息
     * 要求去掉前缀
     * @param env 数据库环境
     * @return SQLResult 查询结果
     */
    public SQLResult showSchema(AbstractDatabaseEnvironment env) throws SQLException {
        try(Connection conn = dataSource.getConnection()){
            Statement stmt = conn.createStatement();
            String where = "WHERE schema_name LIKE '%" + SQLTrim.generateSuffix(env.getUuid()) + "'";
            stmt.execute("SELECT schema_name FROM information_schema.schemata " + where);
            SQLResult result = StatementTool.getResult(stmt);
            SQLTrim.removeColumnSuffix("schema_name", result, env.getUuid());
            return result;
        }
    }

    /**
     * 查询PG_USER表找出属于该环境的所有用户信息
     * @param env 数据库环境
     * @return SQLResult 查询结果
     */
    public SQLResult showUser(AbstractDatabaseEnvironment env) throws SQLException {
        try(Connection conn = dataSource.getConnection()){
            Statement stmt = conn.createStatement();
            String where = "WHERE usename LIKE '%" + SQLTrim.generateSuffix(env.getUuid()) + "'";
            stmt.execute("SELECT usename FROM PG_USER " + where);
            SQLResult result = StatementTool.getResult(stmt);
            SQLTrim.removeColumnSuffix("usename", result, env.getUuid());
            return result;
        }
    }

    public SQLResult showTables(AbstractDatabaseEnvironment env) throws SQLException {
        try(Connection conn = dataSource.getConnection()){
            Statement stmt = conn.createStatement();
            String where = "WHERE tableowner='" + env.getUsername() + "'";
            stmt.execute("SELECT schemaname, tablename FROM PG_TABLES " + where);
            SQLResult result =  StatementTool.getResult(stmt);
            SQLTrim.removeColumnSuffix("schemaname", result, env.getUuid());
            return result;
        }
    }
}
